package com.spmsys.util;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.serializer.SerializerFeature;
import org.apache.commons.dbutils.QueryRunner;
import org.jetbrains.annotations.NotNull;

import javax.sql.DataSource;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;

/**
 * 数据库操作类
 */
@SuppressWarnings("unchecked")
public class Database implements com.spmsys.mapper.Database {
    private DataSource dataSourceSys;
    private DataSource dataSource;
    private String owner = null;
    private String createBy = null;
    private String updateBy = null;

    public Database() {
    }

    public Database(DataSource dataSourceSys, DataSource dataSource) {
        this.dataSourceSys = dataSourceSys;
        this.dataSource = dataSource;
    }

    public Database(DataSource dataSourceSys, DataSource dataSource, String owner, String createBy, String updateBy) {
        this.dataSourceSys = dataSourceSys;
        this.dataSource = dataSource;
        this.owner = owner;
        this.createBy = createBy;
        this.updateBy = updateBy;
    }

    public DataSource getDataSourceSys() {
        return dataSourceSys;
    }

    public DataSource getDataSource() {
        return dataSource;
    }

    public String getOwner() {
        return owner;
    }

    public String getCreateBy() {
        return createBy;
    }

    public String getUpdateBy() {
        return updateBy;
    }

    public void setDataSourceSys(DataSource dataSourceSys) {
        this.dataSourceSys = dataSourceSys;
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void setOwner(String owner) {
        this.owner = owner;
    }

    public void setCreateBy(String createBy) {
        this.createBy = createBy;
    }

    public void setUpdateBy(String updateBy) {
        this.updateBy = updateBy;
    }

    /**
     * 查询条件转sql语句方法
     *
     * @param wheres 查询条件
     * @return sql语句
     */
    public String whereToSql(JSONArray wheres) {
        StringBuilder sql = new StringBuilder();
        Map<String, String> logs = new HashMap<>() {{
            put("and", "and");
            put("or", "or");
        }};
        Map<String, String> rels = new HashMap<>() {{
            put("eq", "=");
            put("neq", "!=");
            put("lt", "<");
            put("lte", "<=");
            put("gt", ">");
            put("gte", ">=");
            put("like", "like");
            put("not like", "not like");
        }};
        for (Map<String, Object> where : parseArray(wheres)) {
            String log = logs.get((String) where.get("log"));
            Object key = where.get("key");
            String rel = rels.get((String) where.get("rel"));
            Object val = where.get("val");
            if (log == null || key == null || rel == null) {
                throw new RuntimeException("log、key、rel为必填参数");
            }
            //拼接sql语句
            sql.append(sql.length() == 0 ? "" : (" " + log + " ")).append(key);
            if (val != null) {
                sql.append(" ").append(rel).append(" ").append(val instanceof String ? ("'" + val + "'") : val);
            } else if ("=".equals(rel) || "!=".equals(rel)) {
                sql.append(" ").append("=".equals(rel) ? "is null" : "is not null");
            } else {
                throw new RuntimeException("val为null时，rel取值（eq、neq）");
            }
        }
        //返回数据
        return sql.toString();
    }

    /**
     * 获取表字段名及字段类型方法
     *
     * @param tableName 表名
     * @return 表字段名及字段类型
     */
    public Map<String, String> getTableFiledNamesAndFiledTypes(String tableName) {
        try (Connection connection = dataSource.getConnection()) {
            Map<String, String> tableFiledNamesAndFiledTypes = new HashMap<>();
            ResultSetMetaData resultSetMetaData = connection.prepareStatement("select * from " + tableName + ";").executeQuery().getMetaData();
            int columnCount = resultSetMetaData.getColumnCount();
            for (int i = 0; i < columnCount; i++) {
                //获取字段名及字段类型
                tableFiledNamesAndFiledTypes.put(resultSetMetaData.getColumnName(i + 1), resultSetMetaData.getColumnTypeName(i + 1));
            }
            //返回数据
            return tableFiledNamesAndFiledTypes;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 获取关联表名方法
     *
     * @param tableName 表名
     * @return 关联表名
     */
    public Map<String, String> getAssociatedTableNames(String tableName) {
        try (Connection connection = dataSource.getConnection()) {
            Map<String, String> associatedTableNames = new HashMap<>();
            ResultSet resultSet = connection.getMetaData().getImportedKeys(connection.getCatalog(), null, tableName);
            while (resultSet.next()) {
                //获取关联表名
                associatedTableNames.put(resultSet.getString(8), resultSet.getString(3));
            }
            //返回数据
            return associatedTableNames;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * json对象转map方法
     *
     * @param jsonObject json对象
     * @return map
     */
    public Map<String, Object> parseObject(JSONObject jsonObject) {
        //返回数据
        return JSONObject.parseObject(JSONObject.toJSONString(jsonObject, SerializerFeature.WriteMapNullValue), Map.class);
    }

    /**
     * json数组转list方法
     *
     * @param jsonArray json数组
     * @return list
     */
    public List<Map<String, Object>> parseArray(@NotNull JSONArray jsonArray) {
        int size = jsonArray.size();
        List<Map<String, Object>> list = new ArrayList<>();
        for (int i = 0; i < size; i++) {
            list.add(JSONObject.parseObject(JSONObject.toJSONString(jsonArray.getJSONObject(i), SerializerFeature.WriteMapNullValue), Map.class));
        }
        //返回数据
        return list;
    }

    @Override
    public Object runTransaction(Function<Connection, Object> func) {
        try (Connection connection = dataSource.getConnection()) {
            //关闭自动提交
            connection.setAutoCommit(false);
            //运行事务
            Object result = func.apply(connection);
            //提交事务
            connection.commit();
            //返回数据
            return result;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public String dbCreate(String tableName, JSONObject record) {
        try {
            StringBuilder table = new StringBuilder(), values = new StringBuilder();
            String id = UUID.randomUUID().toString();
            Long time = System.currentTimeMillis();
            Map<String, Object> recordMap = parseObject(record);
            Map<String, String> tableFiledNamesAndFiledTypes = getTableFiledNamesAndFiledTypes(tableName);
            //设置系统信息
            recordMap.put("id", id);
            recordMap.put("createAt", time);
            recordMap.put("updateAt", time);
            recordMap.put("owner", owner);
            recordMap.put("createBy", createBy);
            recordMap.put("updateBy", updateBy);
            //拼接sql语句
            for (String key : recordMap.keySet()) {
                Object value = (tableFiledNamesAndFiledTypes.get(key) != null && tableFiledNamesAndFiledTypes.get(key).equals("TIMESTAMP")) ? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(recordMap.get(key)) : recordMap.get(key);
                table.append(table.length() == 0 ? "" : ",").append(key);
                values.append(values.length() == 0 ? "" : ",").append(value instanceof String ? ("'" + value + "'") : value);
            }
            //新增记录
            QueryRunner queryRunner = new QueryRunner(dataSource);
            queryRunner.update("insert into " + tableName + "(" + table + ") " + "values(" + values + ");");
            //返回数据
            return id;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public JSONArray dbBatchCreate(String tableName, JSONArray records) {
        try {
            JSONArray ids = new JSONArray();
            StringBuilder table = new StringBuilder(), values = new StringBuilder();
            Long time = System.currentTimeMillis();
            List<Map<String, Object>> recordsList = parseArray(records);
            Map<String, String> tableFiledNamesAndFiledTypes = getTableFiledNamesAndFiledTypes(tableName);
            //拼接sql语句(table)
            for (String key : tableFiledNamesAndFiledTypes.keySet()) {
                table.append(table.length() == 0 ? "" : ",").append(key);
            }
            //拼接sql语句(values)
            for (Map<String, Object> record : recordsList) {
                String id = UUID.randomUUID().toString();
                //设置系统信息
                record.put("id", id);
                record.put("createAt", time);
                record.put("updateAt", time);
                record.put("owner", owner);
                record.put("createBy", createBy);
                record.put("updateBy", updateBy);
                //拼接sql语句
                StringBuilder val = new StringBuilder("(");
                for (String key : tableFiledNamesAndFiledTypes.keySet()) {
                    Object value = (tableFiledNamesAndFiledTypes.get(key).equals("TIMESTAMP") && record.get(key) != null) ? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(record.get(key)) : record.get(key);
                    val.append(val.length() == 1 ? "" : ",").append(value instanceof String ? ("'" + value + "'") : value);
                }
                values.append(val).append("),");
                ids.add(id);
            }
            values.delete(values.length() - 1, values.length());
            //新增记录
            QueryRunner queryRunner = new QueryRunner(dataSource);
            queryRunner.update("insert into " + tableName + "(" + table + ") " + "values" + values + ";");
            //返回数据
            return ids;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer dbDelete(String tableName, String id) {
        try {
            int deleted;
            //删除记录
            QueryRunner queryRunner = new QueryRunner(dataSource);
            deleted = queryRunner.update("delete from " + tableName + " where id = " + "'" + id + "';");
            //返回数据
            return deleted;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer dbBatchDelete(String tableName, JSONArray where) {
        try {
            int deleted;
            String whereSql = where != null ? whereToSql(where) : null;
            //删除记录
            QueryRunner queryRunner = new QueryRunner(dataSource);
            deleted = queryRunner.update("delete from " + tableName + (whereSql != null ? (" where " + whereSql) : "") + ";");
            //返回数据
            return deleted;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer dbUpdate(String tableName, String id, JSONObject record) {
        try {
            int updated;
            StringBuilder set = new StringBuilder();
            Long time = System.currentTimeMillis();
            Map<String, Object> recordMap = parseObject(record);
            Map<String, String> tableFiledNamesAndFiledTypes = getTableFiledNamesAndFiledTypes(tableName);
            //设置系统信息
            recordMap.remove("id");
            recordMap.remove("createAt");
            recordMap.remove("createBy");
            recordMap.put("updateAt", time);
            recordMap.put("updateBy", updateBy);
            if (owner != null) {
                recordMap.put("owner", owner);
            }
            //拼接sql语句
            for (String key : recordMap.keySet()) {
                Object value = (tableFiledNamesAndFiledTypes.get(key) != null && tableFiledNamesAndFiledTypes.get(key).equals("TIMESTAMP")) ? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(recordMap.get(key)) : recordMap.get(key);
                set.append(set.length() == 0 ? "" : ", ").append(key).append(" =").append(" ").append(value instanceof String ? ("'" + value + "'") : value);
            }
            //更新记录
            QueryRunner queryRunner = new QueryRunner(dataSource);
            updated = queryRunner.update("update " + tableName + " set " + set + " where id = " + "'" + id + "';");
            //返回数据
            return updated;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Integer dbBatchUpdate(String tableName, JSONArray where, JSONObject record) {
        try {
            int updated;
            StringBuilder set = new StringBuilder();
            Long time = System.currentTimeMillis();
            String whereSql = where != null ? whereToSql(where) : null;
            Map<String, Object> recordMap = parseObject(record);
            Map<String, String> tableFiledNamesAndFiledTypes = getTableFiledNamesAndFiledTypes(tableName);
            //设置系统信息
            recordMap.remove("id");
            recordMap.remove("createAt");
            recordMap.remove("createBy");
            recordMap.put("updateAt", time);
            recordMap.put("updateBy", updateBy);
            if (owner != null) {
                recordMap.put("owner", owner);
            }
            //拼接sql语句
            for (String key : recordMap.keySet()) {
                Object value = (tableFiledNamesAndFiledTypes.get(key) != null && tableFiledNamesAndFiledTypes.get(key).equals("TIMESTAMP")) ? new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(recordMap.get(key)) : recordMap.get(key);
                set.append(set.length() == 0 ? "" : ", ").append(key).append(" =").append(" ").append(value instanceof String ? ("'" + value + "'") : value);
            }
            //更新记录
            QueryRunner queryRunner = new QueryRunner(dataSource);
            updated = queryRunner.update("update " + tableName + " set " + set + (whereSql != null ? (" where " + whereSql) : "") + ";");
            //返回数据
            return updated;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public JSONObject dbGetItem(String tableName, String id) {
        try {
            List<String> systemFields = new ArrayList<>() {{
                add("owner");
                add("createBy");
                add("updateBy");
            }};
            Map<String, String> associatedTableNames = getAssociatedTableNames(tableName);
            QueryRunner queryRunner = new QueryRunner(dataSource);
            QueryRunner queryRunnerSys = new QueryRunner(dataSourceSys);
            //查询表数据
            JSONObject jsonObject = queryRunner.query("select * from " + tableName + " where id = " + "'" + id + "';", ResultSetTurnJSON::resultSetToJSONObject);
            //查询关联表数据
            for (String fieldName : associatedTableNames.keySet()) {
                String associatedTableName = associatedTableNames.get(fieldName);
                if (jsonObject.get(fieldName) != null) {
                    jsonObject.put("#" + fieldName, (systemFields.contains(fieldName) ? queryRunnerSys : queryRunner).query("select * from " + associatedTableName + " where id = " + "'" + jsonObject.get(fieldName) + "';", ResultSetTurnJSON::resultSetToJSONObject));
                }
            }
            //返回数据
            return jsonObject;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public JSONObject dbGetRecords(String tableName, Integer pageNo, Integer pageSize, String orderBy, String orderType, JSONArray where) {
        try {
            List<String> systemFields = new ArrayList<>() {{
                add("owner");
                add("createBy");
                add("updateBy");
            }};
            String whereSql = where != null ? whereToSql(where) : null;
            String order = (orderBy != null && orderType != null) ? " order by " + orderBy + " " + orderType : " order by id asc";
            String limit = (pageNo != null && pageSize != null) ? " limit " + ((pageNo - 1) * pageSize) + "," + pageSize : " limit 0,10";
            Map<String, String> associatedTableNames = getAssociatedTableNames(tableName);
            QueryRunner queryRunner = new QueryRunner(dataSource);
            QueryRunner queryRunnerSys = new QueryRunner(dataSourceSys);
            //查询表数据
            int total = queryRunner.query("select COUNT(*) from " + tableName + (whereSql != null ? (" where " + whereSql) : "") + ";", (resultSet) -> {
                int count = 0;
                if (resultSet.next()) {
                    count = resultSet.getInt(1);
                }
                return count;
            });
            JSONArray records = queryRunner.query("select * from " + tableName + (whereSql != null ? (" where " + whereSql) : "") + order + limit + ";", ResultSetTurnJSON::resultSetToJSONArray);
            int recordsLength = records.size();
            for (int i = 0; i < recordsLength; i++) {
                JSONObject record = records.getJSONObject(i);
                //查询关联表数据
                for (String fieldName : associatedTableNames.keySet()) {
                    String associatedTableName = associatedTableNames.get(fieldName);
                    if (record.get(fieldName) != null) {
                        record.put("#" + fieldName, (systemFields.contains(fieldName) ? queryRunnerSys : queryRunner).query("select * from " + associatedTableName + " where id = " + "'" + record.get(fieldName) + "';", ResultSetTurnJSON::resultSetToJSONObject));
                    }
                }
            }
            JSONObject jsonObject = new JSONObject();
            jsonObject.put("total", total);
            jsonObject.put("pageNo", pageNo != null ? pageNo : 1);
            jsonObject.put("pageSize", pageSize != null ? pageSize : 10);
            jsonObject.put("records", records);
            //返回数据
            return jsonObject;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }
}
